Solution: Name Columns Explicitly

The best approach is to spell out the names of all the columns we need instead of relying on wildcards or implicit column lists.

Then, we follow this step by inserting a new record in the Accounts table.

All this typing seems burdensome, but it’s worth it in several ways.

Mistake-proofing#

When we specify the columns in the query’s select list, we make our SQL queries more resistant to the errors and confusion described earlier.

Remember poka-yoke?

The following are a few points to remember when we specify the columns in a query’s select list:

• If a column has been repositioned in the table, it doesn’t change position in the query result.

• If a column has been added to the table, it doesn’t appear in the query result.

• If a column has been dropped from the table, the query raises an error — but it’s a good error because we’re led directly to the code that we need to fix instead of being left to hunt for it in the entire code.

We get similar benefits when we specify columns in INSERT statements. The order of columns we specify overrides the order in the table definition, and the values are assigned to the columns we intend. Newly added columns that we haven’t named in our statement are given default values or null. When we reference a column that has been deleted, we get an error, but troubleshooting is easier.

You ain’t gonna need it#

Sometimes when we’re concerned about the scalability and throughput of our software, we should look for possible wasteful use of network bandwidth. The bandwidth of an SQL query can seem harmless during software development and testing, but it can create a lot of problems when our production environment is running thousands of SQL queries per second.

Once we abandon the SQL wildcard, we’re naturally motivated to leave out unneeded columns — it means less typing. This promotes a more efficient use of bandwidth as well.

This is an example of the fail-early principle.

We can give up wildcards anyway#

When we buy a bag of M&M’s candies from the vending machine, the wrapper is a convenience, making it easy to carry the package of candies back to our desk. Once we have opened the bag, however, we need to treat each candy as separate from the packing. If we aren’t careful, the candies can fall, slide and roll away. Some may even attract bugs. But there’s no way to eat a candy until we tear open the bag.

In an SQL query, as soon as we want to apply an expression to a column or use a column alias or exclude columns for the sake of efficiency, we need to break open the “container” provided by the wildcard. We lose the convenience of treating the collection of columns as a single package, but we gain access to all of its contents.

We’ll inevitably need to treat some columns in a query individually by employing a column alias or a function or removing a column from the list. If we skip wildcards from the beginning, it’ll be easier to change the query later.